# @Data  : 2019/11/29 15:05
# @Author: 
# @File  : DBOperate.py
import time
import traceback

from ..assistant import FwVariables
from DatabaseLibrary import DatabaseLibrary
from robot.api import logger
from robot.libraries.BuiltIn import BuiltIn
from Lib.zcm.utils.http_interface import http_interface_obj
from JSONLibrary import JSONLibrary
from Lib.constants import jars_dict


class DBOperate(DatabaseLibrary):
    def __init__(self, link_name, db_type):
        super().__init__()
        self.dbApi = FwVariables.get_api_module(link_name)
        self.url = FwVariables.get_db_url(link_name)
        # 针对qmdb版本处理url
        if db_type == "qmdb":
            qmdb_version = FwVariables.get_qmdb_version(link_name)
            jars_path = jars_dict.get(qmdb_version, jars_dict["V9"])
            self.url += ", '" + jars_path + "'"

        self.nls_language = FwVariables.get_db_language(link_name)
        logger.debug('db language=%s' % self.nls_language)
        if self.nls_language:
            logger.debug('set db language=%s' % self.nls_language)
            import os
            os.environ['NLS_LANG'] = self.nls_language
        self._db_connect()

    def _db_connect(self):
        # logger.info("Connecting to database...")
        old = BuiltIn().set_log_level("WARN")
        self.connect_to_database_using_custom_params(self.dbApi, self.url)
        BuiltIn().set_log_level(old)

    def _db_disconnect(self):
        try:
            self.disconnect_from_database()
        except Exception:
            pass

    def db_query(self, selectStatement):
        sql = selectStatement.strip()
        if sql.endswith(";"):
            sql = sql[:-1]
        return self.query(sql)

    def db_exe_sql_string(self, sqlString, ansTran=False):
        sql = sqlString.strip()
        if sql.endswith(";"):
            sql = sql[:-1]

        # Add by james at 20201204
        # 增加一次可以执行多条sql，如果是mysql的话，测试原生的是不能执行多条sql的
        sql_fragments = sql.split(';')
        for sql_fragment in sql_fragments:
            logger.debug(sql_fragment)
            result = self.execute_sql_string(sql_fragment)

        return result

    # def __enter__(self):
    #     logger.info("Connecting to database...")
    #     # Add by james at 20200914
    #     # cx_Oracle执行时会报编码错误，好像是要读环境变量
    #     if self.nls_language:
    #         logger.debug('set db language=%s' % self.nls_language)
    #         import os
    #         os.environ['NLS_LANG'] = self.nls_language
    #
    #     self._db_connect()
    #     return self
    #
    # def __exit__(self, exc_type, exc_val, exc_tb):
    #     logger.info("Disconnecting from database...")
    #     self._db_disconnect()

    def __del__(self):
        try:
            self._db_disconnect()
        except:
            pass


class ZdaasDBOperate(object):
    @staticmethod
    def db_exe_sql_string(link_name, sqlString):
        """
        关键字 ZDBExeSql 针对zdaas封装
        :param link_name: config.ini中配置的数据section名称
        :param sqlString: sql语句
        """
        sql = sqlString.strip()
        if sql.endswith(";"):
            sql = sql[:-1]

        sql_fragments = sql.split(';')
        for sql_fragment in sql_fragments:
            logger.debug(sql_fragment)
            complete_url = "http://" + FwVariables.get_zdaas_url() + "/zdaas-rest/execute/dml"
            headers = {"Authorization": FwVariables.get_zdaas_auth()}
            body = {'dbName': link_name, 'sql': sql_fragment}
            res = JSONLibrary().convert_string_to_json(
                http_interface_obj.http_post(complete_url, headers=headers, json=body))
            return res

    @staticmethod
    def db_query(link_name, selectStatement):
        """
        关键字 ZDBQuery 针对zdaas封装
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        """
        # 增加适配zdaas模块, 返回字典数组
        complete_url = "http://" + FwVariables.get_zdaas_url() + "/zdaas-rest/execute/dql"
        headers = {"Authorization": FwVariables.get_zdaas_auth()}
        body = {'dbName': link_name, 'sql': selectStatement}
        res = JSONLibrary().convert_string_to_json(
            http_interface_obj.http_post(complete_url, headers=headers, json=body))
        return res["data"] if res["data"] else res["data"]

    @staticmethod
    def db_query_one(link_name, selectStatement):
        """
        关键字 ZDBQueryOne 针对zdaas封装
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        """
        complete_url = "http://" + FwVariables.get_zdaas_url() + "/zdaas-rest/execute/dql"
        headers = {"Authorization": FwVariables.get_zdaas_auth()}
        body = {'dbName': link_name, 'sql': selectStatement}
        i = 0
        # zdaas工具bug, 会出现连接失败情况, 适配三次重连机制
        while i < 3:
            try:
                response = http_interface_obj.http_post(complete_url, headers=headers, json=body)
            except Exception as e:
                i += 1
                logger.warn(e)
                continue
            else:
                res = JSONLibrary().convert_string_to_json(response)
                return res["data"][0] if res["data"] else res["data"]

    @staticmethod
    def db_query_one_value(link_name, selectStatement):
        """
        关键字 ZDBQueryOneValue 针对zdaas封装
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        """
        # 增加适配zdaas模块, 返回字典
        complete_url = "http://" + FwVariables.get_zdaas_url() + "/zdaas-rest/execute/dql"
        headers = {"Authorization": FwVariables.get_zdaas_auth()}
        body = {'dbName': link_name, 'sql': selectStatement}
        i = 0
        while i < 3:
            try:
                response = http_interface_obj.http_post(complete_url, headers=headers, json=body)
            except Exception as e:
                i += 1
                logger.warn(e)
                continue
            else:
                res = JSONLibrary().convert_string_to_json(response)
                return list(res["data"][0].values())[0] if res["data"] else res["data"]


def retry_connect_db(func):
    def inner(*args, **kwargs):
        max_retry = 3
        number = 0
        while number < max_retry:
            try:
                result = func(*args, **kwargs)
                return result
            except Exception as e:
                number += 1
                logger.error("尝试第:{}次".format(number))
                logger.error(traceback.format_exc())
                if number == max_retry:
                    raise Exception(e)
                time.sleep(20)

    return inner


class DBOperation(object):
    """
    数据相关关键字封装
    """

    def __init__(self):
        self.instance = dict()

    def __del__(self):
        if self.instance:
            for link_name in self.instance:
                obj = self.instance[link_name]
                obj._db_disconnect()

    def get_db_operate(self, link_name, db_type):
        if link_name in self.instance:
            # 需要判断是否断开连接了，如果断开，需要重新连接
            db_operate = self.instance[link_name]
            try:
                db_operate._dbconnection.ping()
                return db_operate
            except:
                logger.warn(traceback.format_exc())
                db_operate._db_disconnect()
        db_operate = DBOperate(link_name, db_type)
        self.instance[link_name] = db_operate
        return db_operate

    # @staticmethod
    @retry_connect_db
    def db_exe_sql_string(self, link_name, sqlString, db_type):
        """
        :param link_name: config.ini中配置的数据section名称
        :param sqlString: sql语句
        :param db_type: 数据库类型
        """
        if db_type.lower() != 'zdaas':
            db_operate = self.get_db_operate(link_name, db_type)
            return db_operate.db_exe_sql_string(sqlString, ansTran=False)
        else:
            return ZdaasDBOperate().db_exe_sql_string(link_name, sqlString)

    # @staticmethod
    @retry_connect_db
    def db_query(self, link_name, selectStatement, db_type):
        """
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        :param db_type: 数据库类型
        """
        if db_type.lower() != 'zdaas':
            db_operate = self.get_db_operate(link_name, db_type)
            return db_operate.db_query(selectStatement)

        # 增加适配zdaas模块, 返回字典数组
        else:
            return ZdaasDBOperate().db_query(link_name, selectStatement)

    # @staticmethod
    @retry_connect_db
    def db_query_one(self, link_name, selectStatement, db_type):
        """
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        :param db_type: 数据库类型
        """
        if db_type.lower() != 'zdaas':
            db_operate = self.get_db_operate(link_name, db_type)
            result = db_operate.db_query(selectStatement)
            if len(result) > 0:
                return result[0]
            else:
                return []
        # 增加适配zdaas模块, 返回字典
        else:
            return ZdaasDBOperate().db_query_one(link_name, selectStatement)

    # @staticmethod
    @retry_connect_db
    def db_query_one_value(self, link_name, selectStatement, db_type):
        """
        :param link_name: config.ini中配置的数据section名称
        :param selectStatement: sql语句
        :param db_type: 数据库类型
        """
        if db_type.lower() != 'zdaas':
            db_operate = self.get_db_operate(link_name, db_type)
            result = db_operate.db_query(selectStatement)
            if len(result) > 0:
                return result[0][0]
            else:
                return None
        # 增加适配zdaas模块, 返回字典
        else:
            return ZdaasDBOperate().db_query_one_value(link_name, selectStatement)

    # @staticmethod
    @retry_connect_db
    def db_exe_sql_script(self, link_name, sql_file):
        """
        :param link_name: config.ini中配置的数据section名称
        :param sql_file: sql文件
        """
        # window环境下默认是使用GBK编辑去读文件，这里把所有模块都默认改成UTF8编码
        import _locale
        _locale._getdefaultlocale = (lambda *args: ['en_US', 'utf8'])

        db_operate = self.get_db_operate(link_name, None)
        return db_operate.execute_sql_script(sql_file)


ad_operation = DBOperation()
